package org.csu.mypetstore.persistence.Impl;

import org.csu.mypetstore.domain.Category;
import org.csu.mypetstore.persistence.CategoryDAO;
import org.csu.mypetstore.persistence.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
//查category表
public class CategoryDAOImpl implements CategoryDAO {
    //sql语句
    private static final String GET_CATEGORY_lIST = "SELECT CATID AS categoryId,NAME,DESCN AS description FROM CATEGORY";
    private static final String GET_CATEGORY = "SELECT CATID AS categoryId,NAME,DESCN AS description FROM CATEGORY WHERE CATID=?";

    //获取所有的category
    @Override
    public List<Category> getCategoryList() {
        List<Category> categoryList = new ArrayList<>();
        try{
            //连接
            Connection connection = DBUtil.getConnection();
            //创建preparedStatement对象
            PreparedStatement preparedStatement = connection.prepareStatement(GET_CATEGORY_lIST);
            //执行sql语句，获得结果集
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                Category category = new Category();
                category.setCategoryId(resultSet.getNString(1));
                category.setName(resultSet.getNString(2));
                category.setDescription(resultSet.getString(3));
                categoryList.add(category);
            }
            //关闭连接
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return categoryList;
    }

    //获取具体某个类
    @Override
    public Category getCategory(String categoryId) {
        Category category = null;
        try {
            //连接
            Connection connection = DBUtil.getConnection();
            //创建preparedStatement对象
            PreparedStatement preparedStatement = connection.prepareStatement(GET_CATEGORY);
            //占位符赋值
            preparedStatement.setString(1,categoryId);
            //执行sql语句，获得结果集
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                category = new Category();
                category.setCategoryId(resultSet.getNString(1));
                category.setName(resultSet.getNString(2));
                category.setDescription(resultSet.getString(3));
            }
            //关闭连接
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (Exception e){
            e.printStackTrace();
        }
        return category;
    }
}
